﻿#include "Recorder.h"
#include "ui_Recorder.h"

#include <QDateTime>
#include <QSqlError>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlRecord>

#include "FaceModule/FaceWgt.h"
#include "FingerModule/FingerWgt.h"


Recorder::Recorder(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Recorder)
{
    ui->setupUi(this);
    m_dataBaseName = QCoreApplication::applicationDirPath();
    m_dataBaseName += "/record.db";
    initDatabase();

    // 设置纵轴范围为时间数据的最小和最大值.
    QCustomPlot *plot = ui->curveWgt;
    QSharedPointer<QCPAxisTickerDateTime> ticker(new QCPAxisTickerDateTime);
    //ticker->setDateTimeFormat("yyyy/MM/dd hh:mm:ss");
    ticker->setDateTimeFormat("hh:mm:ss");
    ticker->setTickCount(12);
    plot->yAxis->setTicker(ticker);
    plot->yAxis->setRange(0.0, 12*60*60.0);
    plot->xAxis->setRange(0.0, 31.0);

    m_signInGraph = plot->addGraph();
    m_signInGraph->setPen(QPen(Qt::red));
    m_signInGraph->setName(QString::fromLocal8Bit("上班打卡"));

    m_signOutGraph = plot->addGraph();
    m_signOutGraph->setPen(QPen(Qt::green));
    m_signOutGraph->setName(QString::fromLocal8Bit("下班打卡"));

    plot->setInteractions(QCP::iRangeDrag|QCP::iRangeZoom);
    plot->legend->setVisible(true);
    plot->replot();

    connect(ui->loginFingerBtn, &QPushButton::clicked, this, [this](){
        emit sigLoginFinger(); //注册指纹.
    });
    connect(ui->loginFaceBtn, &QPushButton::clicked, this, [this](){
        emit sigLoginFace(); //注册人脸.
    });
    connect(ui->registerBtn, &QPushButton::clicked, this, [this](){
        QString userId = ui->loginUserIdEdit->text();
        QString userName = ui->userNameEdit->text();
        QString fingerStr = ui->fingerIdEdit->text();
        QString faceStr = ui->faceIdEdit->text();
        QString photo;
        if(userId.isEmpty() || userName.isEmpty()){
            QString msg = QString::fromLocal8Bit("[提示]必须输入员工姓名和员工工号");
            emit sigShowMsg(msg);
            return;
        }
        if(fingerStr.isEmpty() && faceStr.isEmpty()){
            QString msg = QString::fromLocal8Bit("[提示]必须点击录入按钮获取指纹Id或人脸Id");
            emit sigShowMsg(msg);
            return;
        }

        int fingerId = fingerStr.isEmpty() ? -1 : fingerStr.toInt();
        int faceId = faceStr.isEmpty() ? -1 : faceStr.toInt();
        onAdditionEmployee(userId.toInt(), userName, fingerId, faceId, photo);
    });

    connect(ui->cancelBtn, &QPushButton::clicked, this, [this](){
        setShowIndex(1);
        emit sigSearchModel();
    });

    connect(ui->toRegisterBtn, &QPushButton::clicked, this, [this](){
        setShowIndex(0);
        ui->fingerIdEdit->clear();
        ui->faceIdEdit->clear();
    });
    connect(ui->searchUserIdBtn, &QPushButton::clicked, this, [this](){
        QString userIdStr = ui->searchUserIdEdit->text();
        if(!userIdStr.isEmpty()){
            onSearchEmployee(userIdStr.toInt());
        }
        else {
            QString msg = QString::fromLocal8Bit("[错误]请输入员工工号后点击搜索");
            emit sigShowMsg(msg);
        }
    });
    connect(ui->deleteUserBtn, &QPushButton::clicked, this, [this](){
        QString userIdStr = ui->searchUserIdEdit->text();
        if(!userIdStr.isEmpty()){
            onRemoveEmployee(userIdStr.toInt());
        }
        else {
            QString msg = QString::fromLocal8Bit("[错误]请输入员工工号后点击删除");
            emit sigShowMsg(msg);
        }
    });
    connect(ui->filterBtn, &QPushButton::clicked, this, [this](){
        QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName(m_dataBaseName);
        if (!db.open()) {
            return;
        }
        QString userIdStr = ui->filterUsedIdEdit->text();
        QString monthStr = ui->monthBox->currentText();
        if (!userIdStr.isEmpty()) {
            QString deleteSql2 = QString("SELECT * FROM AttendanceInfo WHERE UserId=%1 "
                                         "AND SignMonth='%2';").arg(userIdStr).arg(monthStr);
            QSqlQuery query2(db);
            if (!query2.exec(deleteSql2)) {
                return;
            }

            m_attendanceInfoModel.setQuery(query2);
            ui->tableView_2->setModel(&m_attendanceInfoModel);

            //统计考勤数据.
            QTime workHour(0, 0, 0); //总工时数.
            int lateCnt = 0;    //迟到次数.
            int earlyCnt = 0;   //早退次数.
            int lackCnt = 0;    //缺卡次数.
            QVector<double> x, y1, y2;
            for(int row=0; row<m_attendanceInfoModel.rowCount(); ++row){
                QSqlRecord record = m_attendanceInfoModel.record(row);
                QString signDayStr = record.value(4).toString();
                QString signInStr = record.value(5).toString();
                QString signOutStr = record.value(6).toString();
                QTime signInTime = QTime::fromString(signInStr, "hh:mm:ss");
                QTime signOutTime = QTime::fromString(signOutStr, "hh:mm:ss");

                if(!signInStr.isEmpty() && !signOutStr.isEmpty()){
                    double signIn = signInTime.msecsSinceStartOfDay()/1000 - 8*60*60; //东八区.
                    double signOut = signOutTime.msecsSinceStartOfDay()/1000 - 8*60*60;
                    y1.append(signIn);
                    y2.append(signOut);
                    x.append(signDayStr.toInt());
                    workHour = workHour.addSecs(signInTime.secsTo(signOutTime));
                }

                if(signOutStr.isEmpty()){
                    ++lackCnt;
                }
                if(!signInStr.isEmpty() && signInTime > QTime::fromString("08:45:59", "hh:mm:ss")){
                    ++lateCnt;
                }
                if(!signOutStr.isEmpty() && signOutTime < QTime::fromString("18:00:00", "hh:mm:ss")){
                    ++earlyCnt;
                }
            }

            QCustomPlot *plot = ui->curveWgt; //更新考勤曲线.
            m_signInGraph->data().clear();
            m_signOutGraph->data().clear();
            m_signInGraph->setData(x, y1);
            m_signOutGraph->setData(x, y2);
            plot->replot();

            ui->workHourLab->setText(workHour.toString("hh:mm"));
            ui->lateCntLab->setText(QString::number(lateCnt));
            ui->earlyCntLab->setText(QString::number(earlyCnt));
            ui->lackCntLab->setText(QString::number(lackCnt));
        }
        db.close();
    });
}

Recorder::~Recorder()
{
    delete ui;
}

void Recorder::setShowIndex(int index)
{
    ui->stackedWidget->setCurrentIndex(index);
    refreshTable();
}

//模块注册结束.
void Recorder::onModuleLoginOver(bool flag, int featureId, QString info)
{
    //更新界面控件.
    FingerWgt *finger = qobject_cast<FingerWgt *>(QObject::sender());
    if(nullptr != finger){
        ui->fingerIdEdit->setText(QString::number(featureId));
        ui->infoLab->setText(info);
    }
    FaceWgt *face = qobject_cast<FaceWgt *>(QObject::sender());
    if(nullptr != face){
        if(flag){
            ui->faceIdEdit->setText(QString::number(featureId));
        }
        ui->infoLab->setText(info);
    }
}

//初始化数据库表.
void Recorder::initDatabase()
{
    QString msg;
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(m_dataBaseName);
    if (!db.open()) {
        msg = QString::fromLocal8Bit("[错误]打开数据库失败:%1").arg(db.lastError().text());
        emit sigShowMsg(msg);
        return;
    }

    QString employeeTableSql =
            "CREATE TABLE IF NOT EXISTS EmployeeInfo("
            "UserId INTEGER PRIMARY KEY AUTOINCREMENT,"
            "UserName TEXT NOT NULL,"
            "FingerId INTEGER,"
            "FaceId INTEGER,"
            "PhotoPath TEXT);";

    QSqlQuery query1(db);
    if (!query1.exec(employeeTableSql)) {
        msg = QString::fromLocal8Bit("[错误]创建员工信息表失败:%1").arg(query1.lastError().text());
        emit sigShowMsg(msg);
        return;
    }

    QString attendanceTableSql =
            "CREATE TABLE IF NOT EXISTS AttendanceInfo("
            "RecordId INTEGER PRIMARY KEY AUTOINCREMENT,"
            "UserId INTEGER,"
            "UserName TEXT NOT NULL,"
            "SignMonth TEXT NOT NULL,"
            "SignDay TEXT NOT NULL,"
            "SignInTime TEXT,"
            "SignOutTime TEXT);";

    QSqlQuery query2(db);
    if (!query2.exec(attendanceTableSql)) {
        msg = QString::fromLocal8Bit("[错误]创建考勤记录表失败:%1").arg(query2.lastError().text());
        emit sigShowMsg(msg);
        return;
    }

    db.close();
    return;
}

//新增员工.
bool Recorder::onAdditionEmployee(int userId, QString userName, int fingerId, int faceId, QString photo)
{
    QString msg;
    if(-1 == fingerId && -1 == faceId){
        msg = QString::fromLocal8Bit("[提示]指纹或人脸至少录制其一");
        emit sigShowMsg(msg);
        return false;
    }

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(m_dataBaseName);
    if (!db.open()) {
        msg = QString::fromLocal8Bit("[错误]打开数据库失败:%1").arg(db.lastError().text());
        emit sigShowMsg(msg);
        return false;
    }

    QSqlQuery query(db);
    //UserId查重.
    QString dupSignSql;
    dupSignSql = QString("SELECT * FROM EmployeeInfo WHERE UserId = %1;").arg(userId);
    if (!query.exec(dupSignSql)) {
        msg = QString::fromLocal8Bit("[错误]用户id查重sql执行失败:%1").arg(query.lastError().text());
        emit sigShowMsg(msg);
        return false;
    }
    if(query.next()){
        msg = QString::fromLocal8Bit("[错误]用户id%1在数据库中已被占用").arg(fingerId);
        emit sigShowMsg(msg);
        return false;
    }

    //FingerId查重.
    if(-1 != fingerId){
        dupSignSql = QString("SELECT * FROM EmployeeInfo WHERE FingerId = %1;").arg(fingerId);
        if (!query.exec(dupSignSql)) {
            msg = QString::fromLocal8Bit("[错误]指纹查重sql执行失败:%1").arg(query.lastError().text());
            emit sigShowMsg(msg);
            return false;
        }
        if(query.next()){
            msg = QString::fromLocal8Bit("[错误]指纹id%1在数据库中已被占用").arg(fingerId);
            emit sigShowMsg(msg);
            return false;
        }
    }
    //FaceId查重.
    if(-1 != faceId){
        dupSignSql = QString("SELECT * FROM EmployeeInfo WHERE FaceId = %1;").arg(faceId);
        if (!query.exec(dupSignSql)) {
            msg = QString::fromLocal8Bit("[错误]人脸查重sql执行失败:%1").arg(query.lastError().text());
            emit sigShowMsg(msg);
            return false;
        }
        if(query.next()){
            msg = QString::fromLocal8Bit("[错误]人脸id%1在数据库中已被占用").arg(faceId);
            emit sigShowMsg(msg);
            return false;
        }
    }

    QString insertSql =
            "INSERT INTO EmployeeInfo (UserId,UserName,FingerId,FaceId,PhotoPath)"
            "VALUES(?, ?, ?, ?, ?);";

    query.prepare(insertSql);
    query.bindValue(0, userId);
    query.bindValue(1, userName);
    query.bindValue(2, fingerId);
    query.bindValue(3, faceId);
    query.bindValue(4, photo);

    if (!query.exec()){
        msg = QString::fromLocal8Bit("[错误]插入员工信息sql执行失败:%1").arg(query.lastError().text());
        emit sigShowMsg(msg);
        return false;
    }
    msg = QString::fromLocal8Bit("[提示]已录入员工%1信息").arg(userId);
    emit sigShowMsg(msg);

    refreshTable(true, false);
    return true;
}

//删除员工.
bool Recorder::onRemoveEmployee(int userId)
{
    QString msg;
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(m_dataBaseName);
    if (!db.open()) {
        msg = QString::fromLocal8Bit("[错误]打开数据库失败:%1").arg(db.lastError().text());
        emit sigShowMsg(msg);
        return false;
    }

    QSqlQuery query(db);
    QString dupSignSql = QString("SELECT * FROM EmployeeInfo WHERE UserId = %1;").arg(userId);
    if (!query.exec(dupSignSql)) {
        msg = QString::fromLocal8Bit("[错误]搜索工号Id的sql执行失败:%1").arg(query.lastError().text());
        emit sigShowMsg(msg);
        return false;
    }
    if(!query.next()){
        msg = QString::fromLocal8Bit("[错误]工号id%1在数据库中不存在").arg(userId);
        emit sigShowMsg(msg);
        return false;
    }

    int fingerId = query.value("FingerId").toInt();
    emit sigRemoveFinger(fingerId);

    int faceId = query.value("FaceId").toInt();
    emit sigRemoveFace(faceId);

    QString deleteSql = QString("DELETE FROM EmployeeInfo WHERE UserId=%1;").arg(userId);
    if (!query.exec(deleteSql)){
        msg = QString::fromLocal8Bit("[错误]删除员工个人信息sql执行失败:%1").arg(query.lastError().text());
        emit sigShowMsg(msg);
        return false;
    }
    deleteSql = QString("DELETE FROM AttendanceInfo WHERE UserId=%1;").arg(userId);
    if (!query.exec(deleteSql)){
        msg = QString::fromLocal8Bit("[错误]删除员工考勤信息sql执行失败:%1").arg(query.lastError().text());
        emit sigShowMsg(msg);
        return false;
    }

    msg = QString::fromLocal8Bit("[提示]已删除员工信息%1").arg(userId);
    emit sigShowMsg(msg);

    refreshTable();
    return true;
}


//查找员工.
void Recorder::onSearchEmployee(int userId)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(m_dataBaseName);
    if (!db.open()) {
        return;
    }

    QString searchSql = QString("SELECT * FROM EmployeeInfo WHERE UserId=%1;").arg(userId);
    QSqlQuery query(db);
    if (!query.exec(searchSql)) {
        return;
    }
    m_userInfoModel.setQuery(query);
    ui->tableView->setModel(&m_userInfoModel);

    db.close();
}

//员工签到.
void Recorder::onUserSign(int featureId)
{
    QString msg;
    //数据库操作，查找这个ID对应的用户.
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(m_dataBaseName);
    if (!db.open()) {
        msg = QString::fromLocal8Bit("[错误]打开数据库失败:%1").arg(db.lastError().text());
        emit sigShowMsg(msg);
        return;
    }

    //判断是指纹签到含还是人脸签到.
    QString searchSql;
    FingerWgt *finger = qobject_cast<FingerWgt *>(QObject::sender());
    FaceWgt *face = qobject_cast<FaceWgt *>(QObject::sender());
    QSqlQuery query(db);
    if(finger){
        searchSql = QString("SELECT * FROM EmployeeInfo WHERE FingerId=%1;").arg(featureId);
        if (!query.exec(searchSql)) {
            msg = QString::fromLocal8Bit("[错误]指纹搜索sql执行失败:%1").arg(query.lastError().text());
            emit sigShowMsg(msg);
            return;
        }
        if(!query.next()){
            msg = QString::fromLocal8Bit("[错误]指纹id%1不存在于数据库").arg(featureId);
            emit sigShowMsg(msg);
            return;
        }
    }
    else {
        searchSql = QString("SELECT * FROM EmployeeInfo WHERE FaceId=%1;").arg(featureId);
        if (!query.exec(searchSql)) {
            msg = QString::fromLocal8Bit("[错误]指纹搜索sql执行失败:%1").arg(query.lastError().text());
            emit sigShowMsg(msg);
            return;
        }
        if(!query.next()){
            msg = QString::fromLocal8Bit("[错误]指纹id%1不存在于数据库").arg(featureId);
            emit sigShowMsg(msg);
            return;
        }
    }

    //获取用户名称.
    int userId = query.value("UserId").toInt();
    QString userName = query.value("UserName").toString();

    //生成打卡记录.
    QDateTime dateTime = QDateTime::currentDateTime();
    QString monthStr = dateTime.toString("MM");
    QString dayStr = dateTime.toString("dd");
    QString timeStr = dateTime.toString("hh:mm:ss");
    searchSql = QString("SELECT * FROM AttendanceInfo "
                        "WHERE UserId=%1 AND SignMonth='%2' AND "
                        "SignDay='%3';").arg(userId).arg(monthStr).arg(dayStr);
    if (!query.exec(searchSql)) {
        msg = QString::fromLocal8Bit("[错误]考勤搜索sql执行失败:%1").arg(query.lastError().text());
        emit sigShowMsg(msg);
        return;
    }

    if(!query.next()){ //不存在则插入一条记录.
        QString insertSql = "INSERT INTO AttendanceInfo "
                            "(UserId,UserName,SignMonth,SignDay,SignInTime) "
                            "VALUES (?, ?, ?, ?, ?);";

        query.prepare(insertSql);
        query.bindValue(0, userId);
        query.bindValue(1, userName);
        query.bindValue(2, monthStr);
        query.bindValue(3, dayStr);
        query.bindValue(4, timeStr);
        if (!query.exec()) {
            msg = QString::fromLocal8Bit("[错误]插入考勤记录sql执行失败:%1").arg(query.lastError().text());
            emit sigShowMsg(msg);
            return;
        }

        //播放提示音，谢谢.
        QString text = QString::fromLocal8Bit("%1上班打卡，谢谢").arg(userName);
        emit sigShowMsg(text);
    }
    else { //存在则修改记录.
        QString updateSql = QString("UPDATE AttendanceInfo "
                                    "SET SignOutTime='%1' WHERE UserId=%2 AND SignMonth='%3' "
                                    "AND SignDay='%4';").arg(timeStr).arg(userId).arg(monthStr).arg(dayStr);
        if (!query.exec(updateSql)) {
            msg = QString::fromLocal8Bit("[错误]更新考勤记录sql执行失败:%1").arg(query.lastError().text());
            emit sigShowMsg(msg);
            return;
        }

        //播放提示音，谢谢.
        QString text = QString::fromLocal8Bit("%1下班打卡，您辛苦了").arg(userName);
        emit sigShowMsg(text);
    }

    refreshTable(false, true);
}


//刷新表格.
void Recorder::refreshTable(bool user, bool attendance)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(m_dataBaseName);
    if (!db.open()) {
        return;
    }
    QSqlQuery query(db);
    if(user){
        if (!query.exec("SELECT * FROM EmployeeInfo;")) {
            return;
        }
        m_userInfoModel.setQuery(query);
        ui->tableView->setModel(&m_userInfoModel);
    }
    if(attendance){
        if (!query.exec("SELECT * FROM AttendanceInfo;")) {
            return;
        }
        m_attendanceInfoModel.setQuery(query);
        ui->tableView_2->setModel(&m_attendanceInfoModel);
    }
    db.close();
}

